package com.web;

import java.awt.FileDialog;
import java.awt.Frame;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;

import javax.annotation.Resource;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.service.QueryService;

@Controller
public class QueryController {
	
	@Resource(name="QueryService")
	private QueryService service;
	
	@RequestMapping(value="/saveExcel.do")
	public String saveExcelQueryTable(@RequestParam(value="txtSql")String sql, Model model){
		try
		{
			FileDialog fileopen = new FileDialog(new Frame(),"파일선택",FileDialog.SAVE);		//파일 경로 받기 위한 dialog 창
			fileopen.setVisible(true);		//dialog 창 보이기
			
			String fileRoute = fileopen.getDirectory()+fileopen.getFile()+".xls";		//파일 경로
			
			HashMap map = service.searchQueryByTxt(sql);
			ArrayList list = (ArrayList) map.get("list");
			ArrayList headList = (ArrayList) map.get("headList");
			ArrayList colsArr = new ArrayList();
			WritableWorkbook workbook = null;
			WritableSheet sheet =null;
			
			String message = "";
			if(fileopen.getFile() ==null || fileopen.getDirectory()==null){
				message = "파일 저장 위치가 잘못 설정 되었습니다.";
			} else {
				message = fileRoute+" 위치에 엑셀파일이 저장되었습니다.";
				workbook= Workbook.createWorkbook(new File(fileRoute) );
				sheet =workbook.createSheet("생성테이블", 0);
			}
			
			WritableCellFormat bgFormat = new WritableCellFormat(
					new WritableFont(WritableFont.ARIAL,  10, WritableFont.BOLD,  false, UnderlineStyle.SINGLE,  Colour.DARK_BLUE, ScriptStyle.NORMAL_SCRIPT));		
			bgFormat.setBackground(Colour.LIGHT_TURQUOISE);	//배경색설정(나중에 주입)
			
			sheet.setRowView(0, 450);		//0 행 - 높이 설정
			
			for(int j=0; j<headList.size(); j++){
				sheet.setColumnView(j, 30);			//칼럼 사이즈 조절
			}
			Label label = new Label(0,0,"DB 테이블",bgFormat);	//제목 라벨
			sheet.addCell(label);		//제목 라벨 추가
			
			String tableHead = "<tr><th>NO.</th>";
			String tableTd = "";	
			
			for(int i=0; i<headList.size(); i++){
				Label headLabel = new Label(i, 2 , headList.get(i).toString(),bgFormat);
				sheet.addCell(headLabel);
				tableHead +="<th>";
				tableHead += headList.get(i);
				tableHead +="</th>";
			}
			tableHead += "</tr>";
			
			if(!list.isEmpty()){ 	// 리스트가 있다면
				for(int i=0; i<list.size(); i++){
					colsArr = (ArrayList) list.get(i);
					tableTd += "<tr><td>"+(list.size()-i) +"</td>";
					for(int j=0; j<colsArr.size(); j++){
						if(colsArr.get(j)!=null){
							tableTd += "<td>"+colsArr.get(j) +"</td>";
							Label temp1 = new Label(j, i+3, colsArr.get(j).toString() ); 
							sheet.addCell(temp1);
						} else {
							tableTd += "<td></td>";
						}
					}
					tableTd +="</tr>";
				}
			} else{ //회원이 존재하지 않는 경우 false
				model.addAttribute("message","리스트가 존재하지 않습니다.");
				return "forward:/error.do";
			}
			
			
			workbook.write();
			workbook.close();
			
			model.addAttribute("message", message);
			model.addAttribute("tableHead", tableHead);
			model.addAttribute("tableTd", tableTd);
			model.addAttribute("sqlTxt", sql);
			model.addAttribute("TotalCount", list.size());
			
		} catch(Exception e){
			e.printStackTrace();
			model.addAttribute("message", "에러발생 "+e.getMessage());
			model.addAttribute("sqlTxt", sql);
			return "/queryProgram/querySearchView";
		}
		return "/queryProgram/querySearchView";
	}
	
	@RequestMapping("/searchQueryByTxt.do")
	public String searchQuery(@RequestParam(value="txtSql")String sql, Model model ){
		try{
			HashMap map = service.searchQueryByTxt(sql);
			ArrayList list = (ArrayList) map.get("list");
			ArrayList headList = (ArrayList) map.get("headList");
			ArrayList colsArr = new ArrayList();
			
			String tableHead = "<tr><th>NO.</th>";
			String tableTd = "";	
			
			for(int h=0; h<headList.size();h++){
				tableHead +="<th>";
				tableHead += headList.get(h);
				tableHead +="</th>";
			}
			tableHead += "</tr>";
			
			for(int i=0; i<list.size(); i++){
				colsArr = (ArrayList) list.get(i);
				tableTd += "<tr><td>"+(list.size()-i) +"</td>";
				for(int j=0; j<colsArr.size(); j++){
					if(colsArr.get(j)!=null){
						tableTd += "<td>"+colsArr.get(j) +"</td>";
					} else{
						tableTd +="<td></td>";
					}
				}
				tableTd +="</tr>";
			}
			
			model.addAttribute("tableHead", tableHead);
			model.addAttribute("tableTd", tableTd);
			model.addAttribute("sqlTxt", sql);
			model.addAttribute("TotalCount", list.size());
			
		} catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			model.addAttribute("message", "에러발생 "+e.getMessage());
			model.addAttribute("sqlTxt", sql);
			return "/queryProgram/querySearchView";
		}
		
		return "/queryProgram/querySearchView";
	}
}
